const pool = require('../db/db');
const express = require('express');
const router = express.Router();
const xlsx = require('node-xlsx');
const fs = require('fs');

// 登录接口
router.post('/userLogin', (req, res) => {
    const data = req.body;
    const sel_user = "select * from user where userName = '" + data.loginInfo.userName + "'";
    pool.query(sel_user, data.loginInfo.userName, (error, results) => {
        if (error) {
            throw error;
        }
        if (results[0] === undefined) {
            res.send("-1"); // -1 表示查询不到，用户不存在，即用户名填写错误
        } else {
            if (results[0].userName == data.loginInfo.userName && results[0].passWord == data.loginInfo.passWord) {
                res.send("0"); // 0 表示用户存在并且密码正确
            } else {
                res.send("1"); // 1 表示用户存在，但密码不正确
            }
        }
    })
});
// 注册接口
router.post('/user-register', (req, res) => {
    const params = req.body;
    console.log(params);
    const sel_user = "select * from user where userName = '" + params.userName + "'";
    const add_sql = "insert into user (userName, userPwd,email) values (?,?,?)";

    pool.query(sel_user, params.userName, (error, results) => {
        if (error) {
            console.log(error);
        }
        if (results.length != 0 && params.userName == results[0].userName) {
            res.send("-1"); // -1 表示用户名已经存在
        } else {
            pool.query(add_sql, [params.userName, params.userPwd, params.email], (err, rst) => {
                if (err) {
                    console.log(err);
                } else {
                    console.log(rst);
                    res.send("0"); // 0 表示用户创建成功
                }
            });
        }
    });
});
// 用户修改密码
router.post('/user-chang-password', (req, res) => {
        const data = req.body;
        const sel_sql = "select * from user where userName = '" + data.username + "'";
        const upd_sql = "update user set userPwd = '" + data.newpass + "' where userName = '" + data.username + "'";

        pool.query(sel_sql, data.username, (error, result) => {
            if (error) {
                console.log(error);
            }
            if (result.length != 0 && data.oldpass != result[0].userPwd) {
                res.send("-1"); //-1表示原始错误
            } else {
                pool.query(upd_sql, [data.newpass, data.username], (err, ret) => {
                    if (err) {
                        console.log(err);
                    } else {
                        console.log(ret);
                        res.send("0"); // 0 表示用户修改密码成功
                    }
                });
            }
        })
    })
    // 用户重置登录密码
router.post("/user-reset-password", (req, res) => {
        const data = req.body;
        const upd_sql = "update user set userPwd = '123456' where userName = '" + data.username + "'";

        pool.query(upd_sql, data.username, (err, ret) => {
            if (err) {
                console.log(err);
            } else {
                console.log(ret);
                res.send("0"); // 0 表示用户重置密码成功
            }
        });
    })
    // 查询数据user表的全部用户名数据
router.get("/getAllUser", (req, res) => {
        let sql = "select userName from user";
        pool.query(sql, (err, result) => {
            if (err) {
                console.log(err);
            } else {
                console.log(result);
                // res.send(result);
                res.json(result); //把数据返回出去
            }
        })
    })
    // 新增用户
router.post("/addUser", (req, res) => {
        const data = req.body;
        const add_user = "insert into user(userName,passWord,userTel,accountName,bankNumber,userType) values (?,?,?,?,?,?)";
        pool.query(add_user, [data.userInfo.userName, data.userInfo.passWord, data.userInfo.userTel, data.userInfo.accountName, data.userInfo.bankNumber, data.userInfo.userType], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示添加用户成功
            }
        })
    })
    // 更新用户
router.post("/updateUser", (req, res) => {
        const data = req.body;
        const update_user = "update user set userName = '" + data.userInfo.userName + "',passWord = '" + data.userInfo.passWord + "',userTel= '" + data.userInfo.userTel + "',accountName= '" + data.userInfo.accountName + "',bankNumber= '" + data.userInfo.bankNumber + "',userType= '" + data.userInfo.userType + "' where id ='" + data.userInfo.userId + "'";
        pool.query(update_user, [data.userInfo.userName, data.userInfo.passWord, data.userInfo.userTel, data.userInfo.accountName, data.userInfo.bankNumber, data.userInfo.userType], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示更新用户成功
            }
        })
    })
    // 更新房间
router.post("/updateRoom", (req, res) => {
        const data = req.body;
        const update_room = "update room set userName = '" + data.roomInfo.userName + "',floor= '" + data.roomInfo.floor + "',roomNumber= '" + data.roomInfo.roomNumber + "',roomType= '" + data.roomInfo.roomType + "',rent= '" + data.roomInfo.rent + "',electy= '" + data.roomInfo.electy + "',water= '" + data.roomInfo.water + "' where id ='" + data.roomInfo.roomId + "'";
        pool.query(update_room, [data.roomInfo.userName, data.roomInfo.floor, data.roomInfo.roomNumber, data.roomInfo.roomType, data.roomInfo.rent, data.roomInfo.electy, data.roomInfo.water], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示更新用户成功
            }
        })
    })
    // 新增房屋
router.post("/addRoom", (req, res) => {
        const data = req.body;
        console.log(data);
        const add_room = "insert into room(userName,floor,roomNumber,roomType,rent,electy,water) values (?,?,?,?,?,?,?)";
        pool.query(add_room, [data.roomInfo.userName, data.roomInfo.floor, data.roomInfo.roomNumber, data.roomInfo.roomType, data.roomInfo.rent, data.roomInfo.electy, data.roomInfo.water], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示添加用户成功
            }
        })
    })
    // 删除用户
router.post("/deleteUser", (req, res) => {
        const data = req.body;
        console.log(data);
        const update_user = "delete from user where id ='" + data.id + "'";
        pool.query(update_user, [], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示删除用户成功
            }
        })
    })
    // 删除房间
router.post("/deleteRoom", (req, res) => {
        const data = req.body;
        console.log(data);
        const update_user = "delete from room where id ='" + data.id + "'";
        pool.query(update_user, [], (err, rst) => {
            if (err) {
                console.log(err);
            } else {
                console.log(rst);
                res.send("1"); //1表示删除用户成功
            }
        })
    })
    // 获取全部用户信息
router.get("/getAllUserList", (req, res) => {
        let sql = "select * from user";
        pool.query(sql, (err, result) => {
            if (err) {
                console.log(err);
            } else {
                res.json(result); //把数据返回出去
            }
        })
    })
    // 获取全部房间信息
router.get("/getAllRoomList", (req, res) => {
    let sql = "select * from room";
    pool.query(sql, (err, result) => {
        if (err) {
            console.log(err);
        } else {
            res.json(result); //把数据返回出去
        }
    })
})

// 获取全部房间信息分组
router.get("/getGroupRoomList", (req, res) => {
        let sql = "select * from room ORDER BY room.floor";
        pool.query(sql, (err, result) => {
            if (err) {
                console.log(err);
            } else {
                res.json(result); //把数据返回出去
            }
        })
    })
    // 录入房间信息
router.post("/roomCostInfo", (req, res) => {
        const data = req.body;
        console.log(data);
        const sel_room = "select * from room where id ='" + data.roomForm.roomId + "'";
        const sel_cost = "select roomNumber,year,month from cost where roomNumber ='" + data.roomForm.roomNumber + "' and year = '" + data.roomForm.year + "' and month = '" + data.roomForm.month + "'";
        const ins_cost = "insert into cost(roomNumber,roomUserName,health,management,elevator,sharedElecty,lastElectyNumber,theElectyNumber,lastWaterNumber,theWaterNumber,electyRate,waterRate,rent,total,year,month,createTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
        pool.query(sel_room, (err, result) => {
            if (err) {
                console.log(err);
            } else {
                console.log(result);
                const electyRate = result[0].electy * (data.roomForm.theElectyNumber - data.roomForm.lastElectyNumber);
                const waterRate = result[0].water * (data.roomForm.theWaterNumber - data.roomForm.lastWaterNumber);
                const rent = result[0].rent;
                const roomUserName = result[0].userName;
                const total = Number(rent) + Number(waterRate) + Number(electyRate) + Number(data.roomForm.health) + Number(data.roomForm.management) + Number(data.roomForm.elevator) + Number(data.roomForm.sharedElecty);
                pool.query(sel_cost, (e, resu) => {
                    if (e) {
                        console.log(e);
                    } else {
                        console.log(resu);
                        if (resu.length != 0 && data.roomForm.roomNumber == resu[0].roomNumber && data.roomForm.month == resu[0].month && data.roomForm.year == resu[0].year) {
                            const upd_cost = "update cost set roomNumber = '" + data.roomForm.roomNumber + "',roomUserName = '" + roomUserName + "',health = '" + data.roomForm.health + "',management = '" + data.roomForm.management + "',elevator = '" + data.roomForm.elevator + "',sharedElecty = '" + data.roomForm.sharedElecty + "',lastElectyNumber = '" + data.roomForm.lastElectyNumber + "',theElectyNumber = '" + data.roomForm.theElectyNumber + "',lastWaterNumber = '" + data.roomForm.lastWaterNumber + "',theWaterNumber = '" + data.roomForm.theWaterNumber + "',electyRate = '" + electyRate + "',waterRate = '" + waterRate + "',rent = '" + rent + "',total = '" + total + "',year = '" + data.roomForm.year + "',month = '" + data.roomForm.month + "',createTime = '" + data.roomForm.createTime + "' where roomNumber ='" + data.roomForm.roomNumber + "'";
                            pool.query(upd_cost, [data.roomForm.roomNumber, roomUserName, data.roomForm.health, data.roomForm.management, data.roomForm.elevator, data.roomForm.sharedElecty, data.roomForm.lastElectyNumber, data.roomForm.theElectyNumber, data.roomForm.lastWaterNumber, data.roomForm.theWaterNumber, electyRate, waterRate, rent, total, data.roomForm.year, data.roomForm.month, data.roomForm.createTime], (err, rss) => {
                                if (err) {
                                    console.log(err);
                                } else {
                                    console.log(rss);
                                    res.send("2"); //2表示更新成功
                                }
                            })
                        } else {
                            pool.query(ins_cost, [data.roomForm.roomNumber, roomUserName, data.roomForm.health, data.roomForm.management, data.roomForm.elevator, data.roomForm.sharedElecty, data.roomForm.lastElectyNumber, data.roomForm.theElectyNumber, data.roomForm.lastWaterNumber, data.roomForm.theWaterNumber, electyRate, waterRate, rent, total, data.roomForm.year, data.roomForm.month, data.roomForm.createTime], (err, rst) => {
                                if (err) {
                                    console.log(err);
                                } else {
                                    console.log(rst);
                                    res.send("1"); //1表示添加成功
                                }
                            })
                        }
                    }

                })
            }
        })
    })
    // 删除房间
router.post("/deleteCost", (req, res) => {
    const data = req.body;
    console.log(data);
    const update_user = "delete from cost where id ='" + data.id + "'";
    pool.query(update_user, [], (err, rst) => {
        if (err) {
            console.log(err);
        } else {
            console.log(rst);
            res.send("1"); //1表示删除用户成功
        }
    })
})

//查找物业信息
router.get("/getPropertyInfo", (req, res) => {
        const sql = "select * from user where userType = '" + 1 + "'";
        pool.query(sql, (err, result) => {
            if (err) {
                console.log(err);
            } else {
                res.json(result);
            }
        })
    })
    // 数据分析
router.get("/getAllUserRoomNumber", (req, res) => {
        const user_sql = "select * FROM user";
        const room_sql = "select * FROM room";
        pool.query(user_sql, (err, result) => {
            if (err) console.log(err)
            else {
                pool.query(room_sql, (e, s) => {
                    if (e) console.log(e);
                    else {
                        res.json([result.length, s.length])
                    }
                })
            }
        })
    })
    // 数据分析统计cost表
router.get("/costDataAnalysis", (req, res) => {
        const data = req.query;
        console.log(data);
        const cost_sql = "select * from cost where year = '" + data.year + "' and month = '" + data.month + "'";
        pool.query(cost_sql, (err, result) => {
            if (err) console.log(err);
            else res.json(result)
        })
    })
    // 数据统计 获取全部数据
router.get("/dataStatistics", (req, res) => {
    const sql = "select * from cost";
    pool.query(sql, (err, result) => {
        if (err) {
            console.log(err);
        } else {
            res.json(result);
        }
    })
})

// 打印数据导出为Excel
router.get("/exportExcle", (req, res) => {
    const reqData = req.query;
    console.log(reqData);
    let cost_sql = "";
    if (reqData.year == '0' || reqData.month == '0') {
        cost_sql = "select * from cost";
    } else {
        cost_sql = "select * from cost where year = '" + reqData.year + "' and month = '" + reqData.month + "'";
    }
    let data = [];
    let title = ['房号', '日期', '租金', '卫生费', '管理费', '电梯费', '水费', '电费', '公摊水电费', '合计'];
    data.push(title);
    pool.query(cost_sql, (err, result) => {
        if (err) {
            console.log(err);
        } else {
            console.log(result);
            result.forEach(item => {
                let arrInner = [];
                arrInner.push(item.roomNumber);
                arrInner.push(item.year + '-' + item.month);
                arrInner.push(item.rent);
                arrInner.push(item.health);
                arrInner.push(item.management);
                arrInner.push(item.elevator);
                arrInner.push(item.electyRate);
                arrInner.push(item.waterRate);
                arrInner.push(item.sharedElecty);
                arrInner.push(item.total);
                data.push(arrInner);
            });
            var sumTotal = 0;
            for (let i = 0; i < result.length; i++) {
                sumTotal += Number(result[i].total);
            }
            let header = ['', '', '', '', '', '', '', '', '总计', sumTotal];
            data.push(header);
            let buffer = xlsx.build([{
                name: 'sheet1',
                data: data
            }]);
            let aDate = new Date();
            let path = 'C:/Users/86151/Desktop/excel_data/宝龙大厦统计表' + aDate.getFullYear() + '年' + (aDate.getMonth() + 1) + '月' + aDate.getDate() + '日' + (aDate.getHours()) + '时' + (aDate.getMinutes()) + '分' + (aDate.getSeconds()) + '秒' + '.xlsx';
            fs.writeFileSync(path, buffer, { 'flag': 'w' });
            console.log(buffer);
            res.send(buffer)
        }
    })
})


module.exports = router;